

In [28]:

    
%load_ext sql
%sql sqlite:///chinook.db









    



The sql extension is already loaded. To reload it, use:
  %reload_ext sql






    Out[28]:





'Connected: None@chinook.db'



In [180]:

    
%%sql
Pragma foreign_key=on;
DROP TABLE if exists Customers;
CREATE TABLE Customers (cust_ID integer(0,0) NOT NULL Primary key, cust_name varchar(0,0), cust_info varchar(0,0)); 

DROP TABLE if exists Invoices;
CREATE TABLE Invoices (invoice_ID integer(0,0) NOT NULL Primary key,
                    customer_id varchar(0,0) NOT NULL,
                    order_sum integer(0,0),
                    invoice_date integer(0,0) NOT NULL,
                    FOREIGN KEY (customer_id) REFERENCES Customers(cust_ID));

DROP TABLE if exists Products;
CREATE TABLE Products (product_id integer(0,0) NOT NULL Primary key,
                       product_name varchar(0,0) NOT NULL, 
                       price inteder(0,0));

DROP TABLE if exists Invoice_details;
CREATE TABLE Invoice_details (invoice_ID integer(0,0) NOT NULL, 
                            product_id integer(0,0) NOT NULL,
                            qty integer(0,0) NOT NULL,
                            primary key (product_id, invoice_id)
                            FOREIGN KEY (product_id) REFERENCES Products(product_id)
                            FOREIGN KEY (invoice_ID) REFERENCES Invoices(invoice_ID));









    



Done.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.
0 rows affected.






    Out[180]:





[]



In [181]:

    
import sqlite3
db=sqlite3.connect('chinook.db')
def insert_customer(name, info):
    cur = db.cursor()
    cur.execute( '''
    SELECT COALESCE(MAX(cust_ID)+1, 1) FROM Customers''')
    ID = cur.fetchone()[0]
    cur.execute('''
        INSERT INTO Customers(cust_ID, cust_name, cust_info) VALUES(?,?,?)''', (ID,name,info))
    db.commit()



In [182]:

    
insert_customer('Microsoft', 'just another OS')
insert_customer('Google', 'lmgfy')



In [183]:

    
%%sql
select * from customers









    



Done.






    Out[183]:





    
        cust_ID
        cust_name
        cust_info
    
    
        1
        Microsoft
        just another OS
    
    
        2
        Google
        lmgfy



In [184]:

    
def insert_products(name,price):
    cur=db.cursor()
    cur.execute('''select coalesce(max(product_id)+1,1) from products''')
    id=cur.fetchone()[0]
    cur.execute('''insert into products(product_id, product_name,price) values(?,?,?)''', (id,name,price))
    db.commit()



In [185]:

    
insert_products('Win10',25000)
insert_products('Google glass', 2000)



In [186]:

    
%sql select * from products









    



Done.






    Out[186]:





    
        product_id
        product_name
        price
    
    
        1
        Win10
        25000
    
    
        2
        Google glass
        2000

Написать функцию для добавления заказа. Вход параметр_1 - имя клиента, параметр_2 - список продуктов вида [['a',1],['b',2]] 1 шаг - проверка, что есть такие продукты и клиены 2 шаг - добавление в таблицы invoices and inv-det



In [187]:

    
def check_client(name):
    cur = db.cursor()
    cur.execute('''select cust_id from customers where cust_name = ?''', (name,))
    try:
        cli_id = cur.fetchone()[0]
        return cli_id
    except TypeError:
        print('Такого клиента не существует')
        return -1
            
    # Проверяем, есть ли такой клиент



In [188]:

    
def check_products(product_list):
    cur = db.cursor()
    res = []
    for a, qty in product_list:
        cur.execute('''select product_id from products where product_name = ? ''', (a, ))
        try: 
            pr_id = cur.fetchone()[0]
            res.append([pr_id, qty])
        except TypeError:
            print('Такой продукт не существует')
            return -1 
    return res



In [189]:

    
def add_invoice(customer_id, invoice_date='2017-04-24'):
    cur = db.cursor()
    cur.execute('''select coalesce(max(invoice_id) + 1, 1) from invoices''')
    inv_id = cur.fetchone()[0]
    cur.execute('''insert into invoices(invoice_id, customer_id, order_sum, invoice_date)
                    VALUES(?,?,NULL,?)''', (inv_id, customer_id, invoice_date))
    db.commit()
    return inv_id



In [190]:

    
def add_products(product_list, inv_id):
    cur = db.cursor()
    for name, qty in product_list:
        cur.execute('''insert into invoice_details(invoice_id, product_id, qty) 
                        values (?,?,?)''', (inv_id, name, qty))
    db.commit()
    return 1



In [191]:

    
def update_invoice(inv_id):
    cur = db.cursor()
    cur.execute('''
    select sum(ii.qty*price)
      from invoice_details ii
     inner join products p
             on ii.product_id = p.product_id 
     where invoice_id = ?''', (str(inv_id)))
    sum_order = cur.fetchone()[0]
    cur.execute('''Update invoices set order_sum = ? where invoice_id = ?''', (sum_order, inv_id))
    db.commit()



In [192]:

    
def add_order(client_name, product_list):
    client_id = check_client(client_name)
    if client_id==-1:
        return -1 
    prod_list = check_products(product_list)
    if prod_list == -1:
        return -2
    inv_id = add_invoice(client_id)
    add_products(prod_list, inv_id)
    update_invoice(inv_id)
    db.commit()



In [193]:

    
add_order('Google', b)



In [194]:

    
%%sql 
SELECT * 
from invoices









    



Done.






    Out[194]:





    
        invoice_ID
        customer_id
        order_sum
        invoice_date
    
    
        1
        2
        260000
        2017-04-24



In [195]:

    
%%sql
select * 
from invoice_Details



In [ ]: